package com.risetechnology.database;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.R;
import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.risetechnology.common.Const;
import com.risetechnology.common.Shared;
import com.risetechnology.data.Ingredient;
import com.risetechnology.data.Recipe;
import com.risetechnology.data.Schedule;
import com.risetechnology.data.ShopIngredient;
import com.risetechnology.data.Shopping;
import com.risetechnology.krystalkitchen1.ActivityCalendar;
import com.risetechnology.krystalkitchen1.ActivityDaySchedule;
import com.risetechnology.krystalkitchen1.ActivityRecipes;
import com.risetechnology.krystalkitchen1.ActivitySelectShoppingList;
import com.risetechnology.krystalkitchen1.ActivityShoppingList;
import com.risetechnology.utils.Utils;

public class DbAdapter {
	private final String TAG = "DbAdapter";

	private SQLiteDatabase db;

	private DbHelper dbHelper;

	private Context context;

	private boolean loaded;

	public DbAdapter(Context ctx) {
		this.context = ctx;
	}

	public DbAdapter open() throws SQLException {
		dbHelper = new DbHelper(context);
		dbHelper.setDbLoaded(loaded);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	/**
	 * Open.
	 * 
	 * @param in
	 *            the in
	 * @return the nfc db adapter
	 * @throws SQLException
	 *             the sQL exception
	 */
	public DbAdapter open(InputStream in) throws SQLException {
		context.deleteDatabase(DbHelper.DATABASE_NAME);
		context.openOrCreateDatabase(DbHelper.DATABASE_NAME,
				Context.MODE_PRIVATE, null);
		File dbFile = context.getDatabasePath(DbHelper.DATABASE_NAME);
		try {
			OutputStream out = new FileOutputStream(dbFile);
			Utils.copy(in, out);
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}
		loaded = true;
		return open();
	}

	public DbAdapter open(String path, boolean saveToApp) throws SQLException {
		Log.d(TAG, "open() start");
		if (!saveToApp) {
			dbHelper = null;
			db = SQLiteDatabase.openDatabase(path, null,
					SQLiteDatabase.OPEN_READWRITE);
			return this;
		}

		InputStream in = null;
		try {
			in = new FileInputStream(path);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return in == null ? null : open(in);
	}

	public void close() {
		if (db != null && db.isOpen())
			db.close();
		if (dbHelper != null)
			dbHelper.close();
	}

	public boolean addTips(String tips) {
		String query = "SELECT * FROM " + DbHelper.MyTips.TABLE_NAME;
		Cursor cursor = db.rawQuery(query, null);
		if (cursor != null && cursor.moveToFirst()) {
			return updateTips(tips);
		} else {
			ContentValues contentValues = new ContentValues();
			contentValues.put(DbHelper.MyTips.CONTENT, tips);

			long num = db.insert(DbHelper.MyTips.TABLE_NAME, null,
					contentValues);
			if (num > 0) {
				return true;
			} else {
				return false;
			}
		}
	}

	public boolean updateTips(String newTips) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.MyTips.CONTENT, newTips);

		int num = db.update(DbHelper.MyTips.TABLE_NAME, contentValues, null,
				null);
		if (num > 0) {
			return true;
		} else {
			return false;
		}
	}

	public String getTips() {
		String tips = null;

		String query = "SELECT * FROM " + DbHelper.MyTips.TABLE_NAME;
		Cursor cursor = db.rawQuery(query, null);

		if (cursor != null && cursor.moveToFirst()) {
			tips = cursor.getString(cursor
					.getColumnIndex(DbHelper.MyTips.CONTENT));
		}

		return tips;
	}

	public boolean addGlossary(String glossary) {
		String query = "SELECT * FROM " + DbHelper.Glossary.TABLE_NAME;
		Cursor cursor = db.rawQuery(query, null);
		if (cursor != null && cursor.moveToFirst()) {
			return updateGlossary(glossary);
		} else {
			ContentValues contentValues = new ContentValues();
			contentValues.put(DbHelper.Glossary.CONTENT, glossary);

			long num = db.insert(DbHelper.Glossary.TABLE_NAME, null,
					contentValues);
			if (num > 0) {
				return true;
			} else {
				return false;
			}
		}
	}

	public boolean updateGlossary(String newGlossary) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Glossary.CONTENT, newGlossary);

		int num = db.update(DbHelper.Glossary.TABLE_NAME, contentValues, null,
				null);
		if (num > 0) {
			return true;
		} else {
			return false;
		}
	}

	public String getGlossary() {
		String glossary = null;

		String query = "SELECT * FROM " + DbHelper.Glossary.TABLE_NAME;
		Cursor cursor = db.rawQuery(query, null);

		if (cursor != null && cursor.moveToFirst()) {
			glossary = cursor.getString(cursor
					.getColumnIndex(DbHelper.Glossary.CONTENT));
		}

		return glossary;
	}

	public long addSchedule(Schedule schedule) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Schedule.TITLE, schedule.getTitle());
		contentValues.put(DbHelper.Schedule.PRE_TIME, schedule.getPreTime());
		contentValues.put(DbHelper.Schedule.SER_TIME, schedule.getSerTime());
		contentValues.put(DbHelper.Schedule.DAY_OF_MONTH,
				schedule.getDayOfMonth());
		contentValues.put(DbHelper.Schedule.MONTH, schedule.getMonth());
		contentValues.put(DbHelper.Schedule.YEAR, schedule.getYear());
		contentValues.put(DbHelper.Schedule.ENTREE_ID, schedule.getEntree());
		contentValues.put(DbHelper.Schedule.MAIN_ID, schedule.getMain());
		contentValues.put(DbHelper.Schedule.DESSERT_ID, schedule.getDessert());
		contentValues.put(DbHelper.Schedule.DRINK_ID, schedule.getDrinks());
		contentValues.put(DbHelper.Schedule.REMINDER,
				schedule.getReminderTime());

		Log.e("date",
				schedule.getDayOfMonth() + schedule.getMonth()
						+ schedule.getYear());

		return db.insert(DbHelper.Schedule.TABLE_NAME, null, contentValues);
	}

	public long addRecipe(Recipe recipe) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Recipe.NAME, recipe.getName());
		contentValues.put(DbHelper.Recipe.SERVES, recipe.getServes());
		contentValues.put(DbHelper.Recipe.MEAL_TYPE, recipe.getMealType());
		contentValues.put(DbHelper.Recipe.DESCRIPTION,
				recipe.getMainDescription());
		contentValues.put(DbHelper.Recipe.RATE_ID, recipe.getRateId());
		contentValues.put(DbHelper.Recipe.IS_FAV, recipe.isFav());
		contentValues.put(DbHelper.Recipe.IMAGE, recipe.getPictureData());
		contentValues.put(DbHelper.Recipe.PART_1_NAME, recipe.getPart1Name());
		contentValues.put(DbHelper.Recipe.PART_1_INGREDIENTS,
				recipe.getPart1Ingredients());
		contentValues.put(DbHelper.Recipe.PART_2_NAME, recipe.getPart2Name());
		contentValues.put(DbHelper.Recipe.PART_2_INGREDIENTS,
				recipe.getPart2Ingredients());
		contentValues.put(DbHelper.Recipe.PART_3_NAME, recipe.getPart3Name());
		contentValues.put(DbHelper.Recipe.PART_3_INGREDIENTS,
				recipe.getPart3Ingredients());
		contentValues.put(DbHelper.Recipe.PART_1_METHOD_NAME,
				recipe.getPart1MethodName());
		contentValues.put(DbHelper.Recipe.PART_1_METHOD,
				recipe.getPart1Method());
		contentValues.put(DbHelper.Recipe.PART_2_METHOD_NAME,
				recipe.getPart2MethodName());
		contentValues.put(DbHelper.Recipe.PART_2_METHOD,
				recipe.getPart2Method());
		contentValues.put(DbHelper.Recipe.PART_3_METHOD_NAME,
				recipe.getPart3MethodName());
		contentValues.put(DbHelper.Recipe.PART_3_METHOD,
				recipe.getPart3Method());

		return db.insert(DbHelper.Recipe.TABLE_NAME, null, contentValues);
	}

	public long addRecipe(Recipe recipe, String packageId) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Recipe.NAME, recipe.getName());
		contentValues.put(DbHelper.Recipe.SERVES, recipe.getServes());
		contentValues.put(DbHelper.Recipe.MEAL_TYPE, recipe.getMealType());
		contentValues.put(DbHelper.Recipe.DESCRIPTION,
				recipe.getMainDescription());
		contentValues.put(DbHelper.Recipe.RATE_ID, recipe.getRateId());
		contentValues.put(DbHelper.Recipe.IS_FAV, recipe.isFav());
		contentValues.put(DbHelper.Recipe.IMAGE, recipe.getPictureData());
		contentValues.put(DbHelper.Recipe.PART_1_NAME, recipe.getPart1Name());
		contentValues.put(DbHelper.Recipe.PART_1_INGREDIENTS,
				recipe.getPart1Ingredients());
		contentValues.put(DbHelper.Recipe.PART_2_NAME, recipe.getPart2Name());
		contentValues.put(DbHelper.Recipe.PART_2_INGREDIENTS,
				recipe.getPart2Ingredients());
		contentValues.put(DbHelper.Recipe.PART_3_NAME, recipe.getPart3Name());
		contentValues.put(DbHelper.Recipe.PART_3_INGREDIENTS,
				recipe.getPart3Ingredients());
		contentValues.put(DbHelper.Recipe.PART_1_METHOD_NAME,
				recipe.getPart1MethodName());
		contentValues.put(DbHelper.Recipe.PART_1_METHOD,
				recipe.getPart1Method());
		contentValues.put(DbHelper.Recipe.PART_2_METHOD_NAME,
				recipe.getPart2MethodName());
		contentValues.put(DbHelper.Recipe.PART_2_METHOD,
				recipe.getPart2Method());
		contentValues.put(DbHelper.Recipe.PART_3_METHOD_NAME,
				recipe.getPart3MethodName());
		contentValues.put(DbHelper.Recipe.PART_3_METHOD,
				recipe.getPart3Method());
		contentValues.put(DbHelper.Recipe.PACKAGE_ID, packageId);
		contentValues.put(DbHelper.Recipe.IS_BLOCKED, recipe.isBlocked());
		contentValues.put(DbHelper.Recipe.SERVING_OPTION, recipe.getServingOptions());

		return db.insert(DbHelper.Recipe.TABLE_NAME, null, contentValues);
	}

	public void clearRecipeByPackageId(String packageId) {
		Cursor cursor = db.query(DbHelper.Recipe.TABLE_NAME,
				new String[] { DbHelper.Recipe.ID }, DbHelper.Recipe.PACKAGE_ID
						+ "=\'" + packageId + "\'", null, null, null,
				DbHelper.Recipe.NAME);
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.ID));
				clearIngredientByRecipeId(id);
			} while (cursor.moveToNext());
		}

		db.delete(DbHelper.Recipe.TABLE_NAME, DbHelper.Recipe.PACKAGE_ID
				+ "=\'" + packageId + "\'", null);
	}

	public void deleteRecipe(int id) {
		clearIngredientByRecipeId(id);
		db.delete(DbHelper.Recipe.TABLE_NAME, DbHelper.Recipe.ID + "=" + id,
				null);
	}

	public void clearIngredientByRecipeId(int recipeId) {
		db.delete(DbHelper.Ingredient.TABLE_NAME, DbHelper.Ingredient.RECIPE_ID
				+ "=" + recipeId, null);
	}

	public void addScheduleRecipe(int sId, int rId) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.ScheduleRecipe.SCHEDULE_ID, sId);
		contentValues.put(DbHelper.ScheduleRecipe.RECIPE_ID, rId);

		db.insert(DbHelper.ScheduleRecipe.TABLE_NAME, null, contentValues);
	}

	public void addScheduleRecipe(int sId, String rId) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.ScheduleRecipe.SCHEDULE_ID, sId);
		contentValues.put(DbHelper.ScheduleRecipe.RECIPE_ID, rId);

		db.insert(DbHelper.ScheduleRecipe.TABLE_NAME, null, contentValues);
	}

	public void addIngredient(Ingredient ing) {
		ContentValues values = new ContentValues();
		values.put(DbHelper.Ingredient.QUANTITY, ing.getQuantity());
		values.put(DbHelper.Ingredient.UNIT, ing.getUnit());
		values.put(DbHelper.Ingredient.NAME, ing.getName());
		values.put(DbHelper.Ingredient.DESCRIPTION, ing.getDescription());
		values.put(DbHelper.Ingredient.RECIPE_ID, ing.getRecipeId());
		values.put(DbHelper.Ingredient.TYPE, ing.getType());

		if (ing.getID() > 0) {
			db.update(DbHelper.Ingredient.TABLE_NAME, values,
					DbHelper.Ingredient.ID + "=" + ing.getID(), null);
		} else {
			db.insert(DbHelper.Ingredient.TABLE_NAME, null, values);
		}
	}

	public ArrayList<Ingredient> getIngByRecipe(int recipeId, int type) {
		ArrayList<Ingredient> ings = new ArrayList<Ingredient>();
		Cursor cursor = db.query(DbHelper.Ingredient.TABLE_NAME, null,
				DbHelper.Ingredient.RECIPE_ID + "=" + recipeId + " AND "
						+ DbHelper.Ingredient.TYPE + "=" + type, null, null,
				null, null);
		if (cursor.moveToFirst()) {
			do {
				Ingredient ing = new Ingredient();
				ing.setID(cursor.getInt(cursor
						.getColumnIndex(DbHelper.Ingredient.ID)));
				ing.setQuantity(cursor.getString(cursor
						.getColumnIndex(DbHelper.Ingredient.QUANTITY)));
				ing.setUnit(cursor.getString(cursor
						.getColumnIndex(DbHelper.Ingredient.UNIT)));
				ing.setName(cursor.getString(cursor
						.getColumnIndex(DbHelper.Ingredient.NAME)));
				ing.setDescription(cursor.getString(cursor
						.getColumnIndex(DbHelper.Ingredient.DESCRIPTION)));
				ing.setRecipeId(recipeId);
				ing.setType(type);
				ings.add(ing);
			} while (cursor.moveToNext());
		}
		return ings;
	}

	public void delIngredient(int id) {
		db.delete(DbHelper.Ingredient.TABLE_NAME, DbHelper.Ingredient.ID + "="
				+ id, null);
	}

	public void delScheduleRecipe(int sId) {
		db.delete(DbHelper.ScheduleRecipe.TABLE_NAME,
				DbHelper.ScheduleRecipe.SCHEDULE_ID + "=" + sId, null);
	}

	public ArrayList<Recipe> getRecipeOfSchedule(int sId) {
		ArrayList<Recipe> recipes = new ArrayList<Recipe>();
		Cursor cursor = db.rawQuery("SELECT recipes.id, "
				+ DbHelper.Recipe.NAME + ", " + DbHelper.Recipe.IMAGE + " "
				+ "FROM " + DbHelper.Recipe.TABLE_NAME
				+ " WHERE recipes.id IN "
				+ "(SELECT schedules_recipes.recipe_id FROM schedules_recipes "
				+ "WHERE schedules_recipes.schedule_id = " + sId + ") "
				+ "AND " + DbHelper.Recipe.IS_BLOCKED + "=0", null);

		if (cursor.moveToFirst()) {
			do {
				// TODO
				Recipe r = new Recipe();
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.ID));
				r.setName(cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.NAME)));
				byte[] b = cursor.getBlob(cursor
						.getColumnIndex(DbHelper.Recipe.IMAGE));
				if (b.length == 0 && id <= 10) {
					r.setImage(context.getResources().getDrawable(
							context.getResources().getIdentifier("img" + id,
									"raw", context.getPackageName())));
				} else {
					r.setPictureData(context.getResources(), b);
				}
				recipes.add(r);
			} while (cursor.moveToNext());
		}
		return recipes;
	}

	public int getCreatedRecipeCount() {
		Cursor cursor = db.query(DbHelper.Recipe.TABLE_NAME,
				new String[] { DbHelper.Recipe.ID }, DbHelper.Recipe.PACKAGE_ID
						+ "=0", null, null, null, DbHelper.Recipe.NAME);
		if (cursor.moveToFirst()) {
			int count = cursor.getCount();
			cursor.close();
			return count;
		} else {
			cursor.close();
			return 0;
		}

	}

	public boolean isPackageAvailable(String packageId) {
		Cursor cursor = db.query(DbHelper.Recipe.TABLE_NAME,
				new String[] { "count(*)" }, DbHelper.Recipe.PACKAGE_ID + "=\'"
						+ packageId + "\'", null, null, null,
				DbHelper.Recipe.NAME);
		cursor.moveToFirst();
		int count = cursor.getInt(0);
		cursor.close();
		return count > 0;
	}

	public ArrayList<Recipe> getRecipeByPackageId(String packageId) {
		ArrayList<Recipe> recipes = new ArrayList<Recipe>();
		Cursor cursor = db.query(DbHelper.Recipe.TABLE_NAME, null,
				DbHelper.Recipe.PACKAGE_ID + "=\'" + packageId + "\'", null,
				null, null, DbHelper.Recipe.NAME);
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.ID));
				String name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.NAME));
				String serves = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVES));
				int mealType = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.MEAL_TYPE));
				String description = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.DESCRIPTION));
				int rateId = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.RATE_ID));
				boolean isFav = (cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.IS_FAV)) == 1);

				byte[] imageData = cursor.getBlob(cursor
						.getColumnIndex(DbHelper.Recipe.IMAGE));
				String part1Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_NAME));
				String part1Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_INGREDIENTS));
				String part2Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_NAME));
				String part2Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_INGREDIENTS));
				String part3Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_NAME));
				String part3Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_INGREDIENTS));
				String part1MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD_NAME));
				String part1Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD));
				String part2MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD_NAME));
				String part2Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD));
				String part3MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD_NAME));
				String part3Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD));
				String servingOption = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVING_OPTION));

				Recipe recipe = new Recipe(id, name, serves, mealType,
						description, rateId, isFav, Utils.byteToDrawable(
								context.getResources(), imageData), part1Name,
						part1Ingredients, part2Name, part2Ingredients,
						part3Name, part3Ingredients, part1MethodName,
						part1Method, part2MethodName, part2Method,
						part3MethodName, part3Method,
						Integer.parseInt(packageId), servingOption);

				if (imageData.length == 1 && id <= 10) {
					recipe.setImage(context.getResources().getDrawable(
							context.getResources().getIdentifier("img" + id,
									"raw", context.getPackageName())));
				}

				recipes.add(recipe);
			} while (cursor.moveToNext());
		}

		return recipes;
	}

	public ArrayList<Recipe> getRecipeByMeal(int type, ArrayList<String> strs) {
		ArrayList<Recipe> recipes = new ArrayList<Recipe>();
		Cursor cursor = db.query(DbHelper.Recipe.TABLE_NAME, null,
				DbHelper.Recipe.MEAL_TYPE + "=" + type + " AND "
						+ DbHelper.Recipe.IS_BLOCKED + "=0", null, null, null,
				DbHelper.Recipe.NAME);
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.ID));
				String name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.NAME));
				String serves = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVES));
				int mealType = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.MEAL_TYPE));
				String description = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.DESCRIPTION));
				int rateId = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.RATE_ID));
				boolean isFav = (cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.IS_FAV)) == 1);
				byte[] imageData = cursor.getBlob(cursor
						.getColumnIndex(DbHelper.Recipe.IMAGE));
				String part1Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_NAME));
				String part1Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_INGREDIENTS));
				String part2Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_NAME));
				String part2Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_INGREDIENTS));
				String part3Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_NAME));
				String part3Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_INGREDIENTS));
				String part1MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD_NAME));
				String part1Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD));
				String part2MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD_NAME));
				String part2Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD));
				String part3MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD_NAME));
				String part3Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD));

				int packageID = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.PACKAGE_ID));

				String servingOption = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVING_OPTION));

				Recipe recipe = new Recipe(id, name, serves, mealType,
						description, rateId, isFav, Utils.byteToDrawable(
								context.getResources(), imageData), part1Name,
						part1Ingredients, part2Name, part2Ingredients,
						part3Name, part3Ingredients, part1MethodName,
						part1Method, part2MethodName, part2Method,
						part3MethodName, part3Method,
						packageID, servingOption);

				if (imageData.length == 1 && id <= 10) {
					recipe.setImage(context.getResources().getDrawable(
							context.getResources().getIdentifier("img" + id,
									"raw", context.getPackageName())));
				}

				recipes.add(recipe);
				if (strs != null)
					strs.add(name);
			} while (cursor.moveToNext());
		}

		if (strs.size() == 0) {
			strs.add("No recipe available");
		} else {
			strs.add(0, "Select recipe");
		}
		return recipes;
	}

	public ArrayList<Recipe> getRecipeByMeal2(int type, ArrayList<String> strs) {
		ArrayList<Recipe> recipes = new ArrayList<Recipe>();
		Cursor cursor = db.query(DbHelper.Recipe.TABLE_NAME, null,
				DbHelper.Recipe.MEAL_TYPE + "=" + type + " AND "
						+ DbHelper.Recipe.IS_BLOCKED + "=0", null, null, null,
				DbHelper.Recipe.NAME);
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.ID));
				String name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.NAME));
				String serves = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVES));
				int mealType = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.MEAL_TYPE));
				String description = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.DESCRIPTION));
				int rateId = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.RATE_ID));
				boolean isFav = (cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.IS_FAV)) == 1);
				byte[] imageData = cursor.getBlob(cursor
						.getColumnIndex(DbHelper.Recipe.IMAGE));
				String part1Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_NAME));
				String part1Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_INGREDIENTS));
				String part2Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_NAME));
				String part2Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_INGREDIENTS));
				String part3Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_NAME));
				String part3Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_INGREDIENTS));
				String part1MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD_NAME));
				String part1Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD));
				String part2MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD_NAME));
				String part2Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD));
				String part3MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD_NAME));
				String part3Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD));
				int packageID = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.PACKAGE_ID));
				String servingOption = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVING_OPTION));
				
				Recipe recipe = new Recipe(id, name, serves, mealType,
						description, rateId, isFav, Utils.byteToDrawable(
								context.getResources(), imageData), part1Name,
						part1Ingredients, part2Name, part2Ingredients,
						part3Name, part3Ingredients, part1MethodName,
						part1Method, part2MethodName, part2Method,
						part3MethodName, part3Method, packageID, servingOption);

				if (imageData.length == 1 && id <= 10) {
					recipe.setImage(context.getResources().getDrawable(
							context.getResources().getIdentifier("img" + id,
									"raw", context.getPackageName())));
				}

				recipes.add(recipe);
				if (strs != null)
					strs.add(name);
			} while (cursor.moveToNext());
		}

		return recipes;
	}

	public void unBlockRecipe(String packageId) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Recipe.IS_BLOCKED, 0);

		db.update(DbHelper.Recipe.TABLE_NAME, contentValues,
				DbHelper.Recipe.PACKAGE_ID + "=" + packageId, null);
	}

	public void updateRecipe(Recipe recipe) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Recipe.NAME, recipe.getName());
		contentValues.put(DbHelper.Recipe.SERVES, recipe.getServes());
		contentValues.put(DbHelper.Recipe.MEAL_TYPE, recipe.getMealType());
		contentValues.put(DbHelper.Recipe.DESCRIPTION,
				recipe.getMainDescription());
		contentValues.put(DbHelper.Recipe.RATE_ID, recipe.getRateId());
		contentValues.put(DbHelper.Recipe.IS_FAV, recipe.isFav());
		contentValues.put(DbHelper.Recipe.IMAGE, recipe.getPictureData());
		contentValues.put(DbHelper.Recipe.PART_1_NAME, recipe.getPart1Name());
		contentValues.put(DbHelper.Recipe.PART_1_INGREDIENTS,
				recipe.getPart1Ingredients());
		contentValues.put(DbHelper.Recipe.PART_2_NAME, recipe.getPart2Name());
		contentValues.put(DbHelper.Recipe.PART_2_INGREDIENTS,
				recipe.getPart2Ingredients());
		contentValues.put(DbHelper.Recipe.PART_3_NAME, recipe.getPart3Name());
		contentValues.put(DbHelper.Recipe.PART_3_INGREDIENTS,
				recipe.getPart3Ingredients());
		contentValues.put(DbHelper.Recipe.PART_1_METHOD_NAME,
				recipe.getPart1MethodName());
		contentValues.put(DbHelper.Recipe.PART_1_METHOD,
				recipe.getPart1Method());
		contentValues.put(DbHelper.Recipe.PART_2_METHOD_NAME,
				recipe.getPart2MethodName());
		contentValues.put(DbHelper.Recipe.PART_2_METHOD,
				recipe.getPart2Method());
		contentValues.put(DbHelper.Recipe.PART_3_METHOD_NAME,
				recipe.getPart3MethodName());
		contentValues.put(DbHelper.Recipe.PART_3_METHOD,
				recipe.getPart3Method());

		db.update(DbHelper.Recipe.TABLE_NAME, contentValues,
				"id=" + recipe.getId(), null);
	}

	public void getRecipesMasalas() {
		if (ActivityRecipes.persistantRecipesListMasalas == null) {
			ActivityRecipes.persistantRecipesListMasalas = new ArrayList<Recipe>();
		} else {
			ActivityRecipes.persistantRecipesListMasalas.clear();
		}

		if (ActivityRecipes.recipesListMasalas == null) {
			ActivityRecipes.recipesListMasalas = new ArrayList<Recipe>();
		} else {
			ActivityRecipes.recipesListMasalas.clear();
		}

		String query = "SELECT * FROM " + DbHelper.Recipe.TABLE_NAME
				+ " WHERE meal_type = " + Const.MASALAS_ID + " AND "
				+ DbHelper.Recipe.IS_BLOCKED + "=0";
		Cursor cursor = db.rawQuery(query, null);

		if (cursor != null && cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.ID));
				String name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.NAME));
				String serves = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVES));
				int mealType = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.MEAL_TYPE));
				String description = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.DESCRIPTION));
				int rateId = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.RATE_ID));
				boolean isFav = (cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.IS_FAV)) == 1);
				byte[] imageData = cursor.getBlob(cursor
						.getColumnIndex(DbHelper.Recipe.IMAGE));
				String part1Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_NAME));
				String part1Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_INGREDIENTS));
				String part2Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_NAME));
				String part2Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_INGREDIENTS));
				String part3Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_NAME));
				String part3Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_INGREDIENTS));
				String part1MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD_NAME));
				String part1Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD));
				String part2MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD_NAME));
				String part2Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD));
				String part3MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD_NAME));
				String part3Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD));
				int packageID = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.PACKAGE_ID));
				String servingOption = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVING_OPTION));
				
				Recipe recipe = new Recipe(id, name, serves, mealType,
						description, rateId, isFav, Utils.byteToDrawable(
								context.getResources(), imageData), part1Name,
						part1Ingredients, part2Name, part2Ingredients,
						part3Name, part3Ingredients, part1MethodName,
						part1Method, part2MethodName, part2Method,
						part3MethodName, part3Method, packageID, servingOption);

				if (imageData.length == 1 && id <= 10) {
					recipe.setImage(context.getResources().getDrawable(
							context.getResources().getIdentifier("img" + id,
									"raw", context.getPackageName())));
				}

				ActivityRecipes.recipesListMasalas.add(recipe);
				ActivityRecipes.persistantRecipesListMasalas.add(recipe);
			} while (cursor.moveToNext());
		}
	}

	public Recipe getRecipeById(int id) {
		Recipe recipe = null;
		String query = "SELECT * FROM " + DbHelper.Recipe.TABLE_NAME
				+ " WHERE id=" + id + " AND " + DbHelper.Recipe.IS_BLOCKED
				+ "=0";

		Cursor cursor = db.rawQuery(query, null);
		if (cursor != null && cursor.moveToFirst()) {
			String name = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.NAME));
			String serves = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.SERVES));
			int mealType = cursor.getInt(cursor
					.getColumnIndex(DbHelper.Recipe.MEAL_TYPE));
			String description = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.DESCRIPTION));
			int rateId = cursor.getInt(cursor
					.getColumnIndex(DbHelper.Recipe.RATE_ID));
			boolean isFav = (cursor.getInt(cursor
					.getColumnIndex(DbHelper.Recipe.IS_FAV)) == 1);
			byte[] imageData = cursor.getBlob(cursor
					.getColumnIndex(DbHelper.Recipe.IMAGE));
			String part1Name = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_1_NAME));
			String part1Ingredients = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_1_INGREDIENTS));
			String part2Name = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_2_NAME));
			String part2Ingredients = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_2_INGREDIENTS));
			String part3Name = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_3_NAME));
			String part3Ingredients = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_3_INGREDIENTS));
			String part1MethodName = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_1_METHOD_NAME));
			String part1Method = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_1_METHOD));
			String part2MethodName = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_2_METHOD_NAME));
			String part2Method = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_2_METHOD));
			String part3MethodName = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_3_METHOD_NAME));
			String part3Method = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.PART_3_METHOD));
			int packageID = cursor.getInt(cursor
					.getColumnIndex(DbHelper.Recipe.PACKAGE_ID));
			String servingOption = cursor.getString(cursor
					.getColumnIndex(DbHelper.Recipe.SERVING_OPTION));
			
			recipe = new Recipe(id, name, serves, mealType, description,
					rateId, isFav, Utils.byteToDrawable(context.getResources(),
							imageData), part1Name, part1Ingredients, part2Name,
					part2Ingredients, part3Name, part3Ingredients,
					part1MethodName, part1Method, part2MethodName, part2Method,
					part3MethodName, part3Method, packageID, servingOption);

			if (imageData.length == 1 && id <= 10) {
				recipe.setImage(context.getResources().getDrawable(
						context.getResources().getIdentifier("img" + id, "raw",
								context.getPackageName())));
			}

		}
		return recipe;
	}

	public void getRecipes(boolean isFavOnly) {
		if (ActivityRecipes.persistantRecipesList == null) {
			ActivityRecipes.persistantRecipesList = new ArrayList<Recipe>();
		} else {
			ActivityRecipes.persistantRecipesList.clear();
		}

		if (ActivityRecipes.recipesList == null) {
			ActivityRecipes.recipesList = new ArrayList<Recipe>();
		} else {
			ActivityRecipes.recipesList.clear();
		}

		String query = "SELECT * FROM " + DbHelper.Recipe.TABLE_NAME
				+ " WHERE meal_type != " + Const.MASALAS_ID + " AND "
				+ DbHelper.Recipe.IS_BLOCKED + "=0";
		if (isFavOnly) {
			query += " AND " + DbHelper.Recipe.IS_FAV + " > 0";
		}
		Cursor cursor = db.rawQuery(query, null);

		if (cursor != null && cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.ID));
				String name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.NAME));
				String serves = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVES));
				int mealType = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.MEAL_TYPE));
				String description = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.DESCRIPTION));
				int rateId = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.RATE_ID));
				boolean isFav = (cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.IS_FAV)) == 1);
				byte[] imageData = cursor.getBlob(cursor
						.getColumnIndex(DbHelper.Recipe.IMAGE));
				String part1Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_NAME));
				String part1Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_INGREDIENTS));
				String part2Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_NAME));
				String part2Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_INGREDIENTS));
				String part3Name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_NAME));
				String part3Ingredients = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_INGREDIENTS));
				String part1MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD_NAME));
				String part1Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_1_METHOD));
				String part2MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD_NAME));
				String part2Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_2_METHOD));
				String part3MethodName = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD_NAME));
				String part3Method = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.PART_3_METHOD));
				int packageID = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Recipe.PACKAGE_ID));
				String servingOption = cursor.getString(cursor
						.getColumnIndex(DbHelper.Recipe.SERVING_OPTION));
				
				Recipe recipe = new Recipe(id, name, serves, mealType,
						description, rateId, isFav, Utils.byteToDrawable(
								context.getResources(), imageData), part1Name,
						part1Ingredients, part2Name, part2Ingredients,
						part3Name, part3Ingredients, part1MethodName,
						part1Method, part2MethodName, part2Method,
						part3MethodName, part3Method, packageID, servingOption);

				if (imageData.length == 1 && id <= 10) {
					recipe.setImage(context.getResources().getDrawable(
							context.getResources().getIdentifier("img" + id,
									"raw", context.getPackageName())));
				}

				ActivityRecipes.recipesList.add(recipe);
				ActivityRecipes.persistantRecipesList.add(recipe);
			} while (cursor.moveToNext());
		}
	}

	public void updateRecipeRate(int id, int rateId) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Recipe.RATE_ID, rateId);

		int num = db.update(DbHelper.Recipe.TABLE_NAME, contentValues,
				DbHelper.Recipe.ID + "=" + id, null);
		Log.e("test", "" + num);
	}

	public void favRecipe(int id, boolean isFav) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Recipe.IS_FAV, isFav);

		db.update(DbHelper.Recipe.TABLE_NAME, contentValues, DbHelper.Recipe.ID
				+ "=" + id, null);
	}

	public void updateSchedule(Schedule schedule) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Schedule.TITLE, schedule.getTitle());
		contentValues.put(DbHelper.Schedule.PRE_TIME, schedule.getPreTime());
		contentValues.put(DbHelper.Schedule.SER_TIME, schedule.getSerTime());
		contentValues.put(DbHelper.Schedule.DAY_OF_MONTH,
				schedule.getDayOfMonth());
		contentValues.put(DbHelper.Schedule.MONTH, schedule.getMonth());
		contentValues.put(DbHelper.Schedule.YEAR, schedule.getYear());
		contentValues.put(DbHelper.Schedule.ENTREE_ID, schedule.getEntree());
		contentValues.put(DbHelper.Schedule.MAIN_ID, schedule.getMain());
		contentValues.put(DbHelper.Schedule.DESSERT_ID, schedule.getDessert());
		contentValues.put(DbHelper.Schedule.DRINK_ID, schedule.getDrinks());
		contentValues.put(DbHelper.Schedule.REMINDER,
				schedule.getReminderTime());

		db.update(DbHelper.Schedule.TABLE_NAME, contentValues,
				"id=" + schedule.getId(), null);
	}

	public ArrayList<Schedule> getScheduleInFuture(long milisec) {
		ArrayList<Schedule> schedules = new ArrayList<Schedule>();
		String query = "SELECT * FROM " + DbHelper.Schedule.TABLE_NAME
				+ " WHERE " + DbHelper.Schedule.REMINDER + ">" + milisec;

		Cursor cursor = db.rawQuery(query, null);
		if (cursor != null && cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Schedule.ID));
				String title = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.TITLE));
				String preTime = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.PRE_TIME));
				String serTime = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.SER_TIME));
				String entree = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.ENTREE_ID));
				String main = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.MAIN_ID));
				String dessert = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.DESSERT_ID));
				String drinks = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.DRINK_ID));
				long reminder = cursor.getLong(cursor
						.getColumnIndex(DbHelper.Schedule.REMINDER));
				Schedule schedule = new Schedule(id, title, preTime, serTime,
						"", "", "", entree, main, dessert, drinks, reminder);

				schedules.add(schedule);
			} while (cursor.moveToNext());
		}

		return schedules;
	}

	public void getSchedule(String dayOfMonth, String month, String year) {
		String query = "SELECT * FROM " + DbHelper.Schedule.TABLE_NAME
				+ " WHERE " + DbHelper.Schedule.DAY_OF_MONTH + "='"
				+ dayOfMonth + "'" + " AND " + DbHelper.Schedule.MONTH + "='"
				+ month + "'" + " AND " + DbHelper.Schedule.YEAR + "='" + year
				+ "'";

		Log.e("get schedule", query);

		if (ActivityDaySchedule.scheduleList == null) {
			ActivityDaySchedule.scheduleList = new ArrayList<Schedule>();
		} else {
			ActivityDaySchedule.scheduleList.clear();
		}
		ArrayList<Recipe> rEntrees, rMains, rDesserts, rDrinks;
		// rEntrees = Shared.dbAdapter.getRecipeByMeal2(0, null);
		// rMains = Shared.dbAdapter.getRecipeByMeal2(1, null);
		// rDesserts = Shared.dbAdapter.getRecipeByMeal2(2, null);
		// rDrinks = Shared.dbAdapter.getRecipeByMeal2(3, null);

		Cursor cursor = db.rawQuery(query, null);
		if (cursor != null && cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Schedule.ID));
				String title = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.TITLE));
				String preTime = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.PRE_TIME));
				String serTime = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.SER_TIME));
				String entree = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.ENTREE_ID));
				String main = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.MAIN_ID));
				String dessert = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.DESSERT_ID));
				String drinks = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.DRINK_ID));
				long reminder = cursor.getLong(cursor
						.getColumnIndex(DbHelper.Schedule.REMINDER));
				Schedule schedule = new Schedule(id, title, preTime, serTime,
						dayOfMonth, month, year, entree, main, dessert, drinks,
						reminder);
				ArrayList<Recipe> sEntrees = new ArrayList<Recipe>();
				if (schedule.getEntree() != null) {
					String entrees[] = schedule.getEntree().split(" ");
					if (entrees.length > 0) {
						for (int i = 0; i < entrees.length; i++)
							if (entrees[i].length() > 0) {
								Recipe recipe = Shared.dbAdapter
										.getRecipeById(Integer
												.parseInt(entrees[i]));
								if (recipe != null) {
									sEntrees.add(recipe);
								}
							}
					}
				}
				schedule.setEntreeRecipes(sEntrees);
				ArrayList<Recipe> sDesserts = new ArrayList<Recipe>();
				if (schedule.getDessert() != null) {
					String desserts[] = schedule.getDessert().split(" ");
					if (desserts.length > 0) {
						for (int i = 0; i < desserts.length; i++)
							if (desserts[i].length() > 0) {
								Recipe recipe = Shared.dbAdapter
										.getRecipeById(Integer
												.parseInt(desserts[i]));
								if (recipe != null) {
									sDesserts.add(recipe);
								}
							}
					}
				}
				schedule.setDessertRecipes(sDesserts);
				ArrayList<Recipe> sMains = new ArrayList<Recipe>();
				if (schedule.getMain() != null) {
					String mains[] = schedule.getMain().split(" ");
					if (mains.length > 0) {
						for (int i = 0; i < mains.length; i++)
							if (mains[i].length() > 0) {
								Recipe recipe = Shared.dbAdapter
										.getRecipeById(Integer
												.parseInt(mains[i]));
								if (recipe != null) {
									sMains.add(recipe);
								}
							}
					}
				}
				schedule.setMainRecipes(sMains);
				ArrayList<Recipe> sDrinks = new ArrayList<Recipe>();
				if (schedule.getDrinks() != null) {
					String drink[] = schedule.getMain().split(" ");
					if (drink.length > 0) {
						for (int i = 0; i < drink.length; i++)
							if (drink[i].length() > 0) {
								Recipe recipe = Shared.dbAdapter
										.getRecipeById(Integer
												.parseInt(drink[i]));
								if (recipe != null) {
									sDrinks.add(recipe);
								}
							}
					}
				}
				schedule.setDrinkRecipes(sDrinks);

				ActivityDaySchedule.scheduleList.add(schedule);
			} while (cursor.moveToNext());
		}
	}

	public void getEventDates(String month) {
		String query = "SELECT * FROM " + DbHelper.Schedule.TABLE_NAME
				+ " WHERE " + DbHelper.Schedule.MONTH + "='" + month + "'";

		Log.e("get event dates", query);

		Cursor cursor = db.rawQuery(query, null);

		if (ActivityCalendar.items == null) {
			ActivityCalendar.items = new ArrayList<String>();
		} else {
			ActivityCalendar.items.clear();
		}

		if (cursor != null && cursor.moveToFirst()) {
			do {
				String dayOfMonth = cursor.getString(cursor
						.getColumnIndex(DbHelper.Schedule.DAY_OF_MONTH));
				ActivityCalendar.items.add(dayOfMonth);
			} while (cursor.moveToNext());
		}
	}

	public void getShoppingList(boolean isSelectShoppingList) {
		String query = "SELECT * FROM " + DbHelper.Shoppings.TABLE_NAME;

		Cursor cursor = db.rawQuery(query, null);

		if (cursor != null && cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(cursor
						.getColumnIndex(DbHelper.Shoppings.ID));
				String name = cursor.getString(cursor
						.getColumnIndex(DbHelper.Shoppings.NAME));
				// String ingredients =
				// cursor.getString(cursor.getColumnIndex(DbHelper.Shoppings.INGREDIENTS));

				Shopping shopping = new Shopping(Integer.toString(id), name);
				if (isSelectShoppingList) {
					ActivitySelectShoppingList.shoppingList.add(shopping);
				} else {
					ActivityShoppingList.shoppingList.add(shopping);
				}
			} while (cursor.moveToNext());
		}
	}

	public long addShoppingList(Shopping shopping) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Shoppings.NAME, shopping.getName());
		// contentValues.put(DbHelper.Shoppings.INGREDIENTS,
		// shopping.getIngredients());

		return db.insert(DbHelper.Shoppings.TABLE_NAME, null, contentValues);
	}

	public void updateShoppingList(Shopping shopping) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.Shoppings.NAME, shopping.getName());

		db.update(DbHelper.Shoppings.TABLE_NAME, contentValues, "id="
				+ shopping.getId(), null);
	}

	public boolean deleteSchedule(int scheduleId) {
		return db.delete(DbHelper.Schedule.TABLE_NAME, DbHelper.Schedule.ID
				+ "='" + scheduleId + "'", null) > 0;
	}

	public boolean deleteShoppingList(String shoppingListId) {
		return db.delete(DbHelper.Shoppings.TABLE_NAME, DbHelper.Shoppings.ID
				+ "='" + shoppingListId + "'", null) > 0;
	}

	public boolean hasEvent(String dayOfMonth, String month, String year) {
		String query = "SELECT * FROM " + DbHelper.Schedule.TABLE_NAME
				+ " WHERE " + DbHelper.Schedule.DAY_OF_MONTH + "='"
				+ dayOfMonth + "'" + " AND " + DbHelper.Schedule.MONTH + "='"
				+ month + "'" + " AND " + DbHelper.Schedule.YEAR + "='" + year
				+ "'";

		Log.e("has event", query);

		Cursor cursor = db.rawQuery(query, null);

		if (cursor != null && cursor.moveToFirst()) {
			if (cursor.getCount() > 0) {
				return true;
			}
		}
		return false;
	}

	public void addShoppingDetail(ShopIngredient sIng) {
		Cursor c = db.query(
				DbHelper.ShoppingsDetail.TABLE_NAME,
				null,
				DbHelper.ShoppingsDetail.SHOPPING_ID + "=" + sIng.getShopID()
						+ " AND " + DbHelper.ShoppingsDetail.UNIT + "='"
						+ sIng.getUnit() + "' AND "
						+ DbHelper.ShoppingsDetail.NAME + "='" + sIng.getName()
						+ "' AND " + DbHelper.ShoppingsDetail.DESCRIPTION
						+ "='" + sIng.getDescription() + "'", null, null, null,
				DbHelper.ShoppingsDetail.ID + " DESC");

		if (c.moveToFirst()) {
			String quantity = c.getString(c
					.getColumnIndex(DbHelper.ShoppingsDetail.QUANTITY));
			float q1 = 0f;
			float q2 = 0f;

			try {
				q1 = Float.parseFloat(quantity);
			} catch (Exception e) {
				// TODO: handle exception
			}
			try {
				q2 = Float.parseFloat(sIng.getQuantity());
			} catch (Exception e) {
				// TODO: handle exception
			}

			if (q1 != 0f && q2 != 0f) {
				sIng.setQuantity(String.format("%.2f", q1 + q2));

				deleteIngredient(c.getInt(c
						.getColumnIndex(DbHelper.ShoppingsDetail.ID)));
			}
		}
		c.close();

		ContentValues contentValues = new ContentValues();
		contentValues.put(DbHelper.ShoppingsDetail.SHOPPING_ID,
				sIng.getShopID());
		contentValues
				.put(DbHelper.ShoppingsDetail.QUANTITY, sIng.getQuantity());
		contentValues.put(DbHelper.ShoppingsDetail.UNIT, sIng.getUnit());
		contentValues.put(DbHelper.ShoppingsDetail.NAME, sIng.getName());
		contentValues.put(DbHelper.ShoppingsDetail.DESCRIPTION,
				sIng.getDescription());

		db.insert(DbHelper.ShoppingsDetail.TABLE_NAME, null, contentValues);
	}

	public void getIngredients(String shoppingListId,
			ArrayList<ShopIngredient> ingredientList) {
		ingredientList.clear();
		String query = "SELECT * FROM " + DbHelper.ShoppingsDetail.TABLE_NAME
				+ " WHERE " + DbHelper.ShoppingsDetail.SHOPPING_ID + "='"
				+ shoppingListId + "' " + "ORDER BY "
				+ DbHelper.ShoppingsDetail.NAME;

		Cursor cursor = db.rawQuery(query, null);
		if (cursor != null && cursor.moveToFirst()) {
			do {
				ShopIngredient i = new ShopIngredient(
						cursor.getInt(cursor
								.getColumnIndex(DbHelper.ShoppingsDetail.ID)),
						cursor.getString(cursor
								.getColumnIndex(DbHelper.ShoppingsDetail.QUANTITY)),
						cursor.getString(cursor
								.getColumnIndex(DbHelper.ShoppingsDetail.UNIT)),
						cursor.getString(cursor
								.getColumnIndex(DbHelper.ShoppingsDetail.NAME)),
						cursor.getString(cursor
								.getColumnIndex(DbHelper.ShoppingsDetail.DESCRIPTION)),
						Integer.parseInt(shoppingListId));

				ingredientList.add(i);
			} while (cursor.moveToNext());
		}
	}

	public void deleteIngredient(int id) {
		db.delete(DbHelper.ShoppingsDetail.TABLE_NAME,
				DbHelper.ShoppingsDetail.ID + "=" + id, null);
	}
}
